package my.assistant.homecashmanager.tally.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import my.assistant.homecashmanager.tally.data.JournalAccount;

import org.assistant.common.exception.GetFromDBException;
import org.assistant.common.exception.SaveIntoDBException;
import org.assistant.common.jdbc.core.RowMapper;
import org.assistant.common.jdbc.dao.BasicDao;

public class TallyDaoImpl extends BasicDao implements TallyDao {
	private class JournalAccountRowMapper implements RowMapper<JournalAccount> {

		public JournalAccount mapRow(ResultSet rs, int index)
				throws SQLException {
			JournalAccount journalAccount = new JournalAccount();
			journalAccount.setId(rs.getString("id_"));
			journalAccount.setDealDate(rs.getDate("deal_date_"));
			journalAccount.setDataFrom(rs.getString("data_from_"));
			journalAccount.setInoutItemId(rs.getLong("inout_item_id_"));
			journalAccount.setInoutItemName(rs.getString("inout_item_name_"));
			journalAccount.setInoutItemType(rs.getString("inouttype_"));
			journalAccount.setInAccountId(rs.getString("inaccount_"));
			journalAccount.setOutAccountId(rs.getString("outaccount_"));
			journalAccount.setMoney(rs.getBigDecimal("money_"));
			journalAccount.setMoneyType(rs.getString("money_type_"));
			journalAccount.setMemo(rs.getString("memo_"));
			journalAccount.setRelationUserId(rs.getString("relation_user_id_"));
			journalAccount.setRelationUserName(rs
				.getString("relation_user_name_"));
			journalAccount.setLogUserId(rs.getString("log_user_id_"));
			journalAccount.setLogUserName(rs.getString("user_name_"));

			return journalAccount;
		}
	}

	/**
	 * 获取收支流水账<br>
	 * TODO 尚未根据家庭、用户进行过滤
	 * 
	 * @throws GetFromDBException
	 */
	public List<JournalAccount> getJournalAccountList()
			throws GetFromDBException {
		StringBuffer sqlBuffer = new StringBuffer();
		sqlBuffer.append("select a.id_,a.deal_date_,");
		sqlBuffer.append("a.data_from_,a.inout_item_id_,b.inout_item_name_,");
		sqlBuffer.append("b.inouttype_,a.inaccount_,");
		sqlBuffer.append("a.outaccount_,a.money_,a.money_type_,");
		sqlBuffer.append("a.memo_,a.relation_user_id_,c.relation_user_name_,");
		sqlBuffer.append("a.log_user_id_,d.user_name_ ");
		sqlBuffer.append("from accounting_journal a,dm_inout_item b,");
		sqlBuffer.append("common_relation_users c,common_users d ");
		sqlBuffer.append("where a.inout_item_id_=b.inout_item_id_ ");
		sqlBuffer.append("and a.relation_user_id_=c.relation_user_id_ ");
		sqlBuffer.append("and a.log_user_id_=d.user_id_ ");
		// sqlBuffer.append("and a.log_user_home_id_=?");
		sqlBuffer.append("order by a.deal_date_,a.log_time_ ");

		List<JournalAccount> list;
		try {
			list = getJdbcTemplate().queryForList(
				sqlBuffer.toString(), new JournalAccountRowMapper());
		} catch (SQLException e) {
			e.printStackTrace();
			throw new GetFromDBException("查询数据时出错啦：" + e.getMessage());
		}

		return list;
	}

	/**
	 * 增加一条收支记录
	 * 
	 * @param journalAccount
	 * @throws SaveIntoDBException
	 */
	public void insertJournalAccount(JournalAccount journalAccount)
			throws SaveIntoDBException {
		StringBuffer sqlBuffer = new StringBuffer("insert into ");
		sqlBuffer.append("accounting_journal(id_,deal_date_,");
		sqlBuffer.append("data_from_,inout_item_id_,");
		sqlBuffer.append("inaccount_,outaccount_,");
		sqlBuffer.append("money_,money_type_,memo_,");
		sqlBuffer.append("relation_user_id_,log_user_id_,");
		sqlBuffer.append("log_user_home_id_,log_time_) ");
		sqlBuffer.append("values(?,?,?,?,?,?,?,?,?,?,?,?,now())");

		try {
			getJdbcTemplate().update(
				sqlBuffer.toString(), journalAccount.getId(),
				journalAccount.getDealDate(), journalAccount.getDataFrom(),
				journalAccount.getInoutItemId(),
				journalAccount.getInAccountId(),
				journalAccount.getOutAccountId(), journalAccount.getMoney(),
				journalAccount.getMoneyType(), journalAccount.getMemo(),
				journalAccount.getRelationUserId(),
				journalAccount.getLogUserId(),
				journalAccount.getLogUserHomeId());
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SaveIntoDBException("保存数据时出错啦：" + e.getMessage());
		}
	}

	/**
	 * 修改一条收支记录
	 * 
	 * @param journalAccount
	 * @throws SaveIntoDBException
	 */
	public void updateJournalAccount(JournalAccount journalAccount)
			throws SaveIntoDBException {
		StringBuffer sqlBuffer = new StringBuffer();
		sqlBuffer.append("update accounting_journal ");
		sqlBuffer.append("set deal_date_=?,");
		sqlBuffer.append("inout_item_id_=?,");
		sqlBuffer.append("inaccount_=?,");
		sqlBuffer.append("outaccount_=?,");
		sqlBuffer.append("money_=?,");
		sqlBuffer.append("money_type_=?,");
		sqlBuffer.append("memo_=?,");
		sqlBuffer.append("relation_user_id_=?,");
		sqlBuffer.append("log_user_id_=?,");
		sqlBuffer.append("log_time_=now() ");
		sqlBuffer.append("where id_=?");

		try {
			getJdbcTemplate().update(
				sqlBuffer.toString(), journalAccount.getDealDate(),
				journalAccount.getInoutItemId(),
				journalAccount.getInAccountId(),
				journalAccount.getOutAccountId(), journalAccount.getMoney(),
				journalAccount.getMoneyType(), journalAccount.getMemo(),
				journalAccount.getRelationUserId(),
				journalAccount.getLogUserId(), journalAccount.getId());
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SaveIntoDBException("保存数据时出错啦：" + e.getMessage());
		}
	}
}
